﻿Imports System.Data.OleDb

Module XL_SoTietKiem
    Structure SoTietKiem
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _nguoiDung As Integer
        Property NguoiDung() As Integer
            Get
                Return _nguoiDung
            End Get
            Set(ByVal value As Integer)
                _nguoiDung = value
            End Set
        End Property
        Private _ngayLap As Date
        Property NgayLap() As Date
            Get
                Return _ngayLap
            End Get
            Set(ByVal value As Date)
                _ngayLap = value
            End Set
        End Property
        Private _taiKhoan As String
        Property TaiKhoan() As String
            Get
                Return _taiKhoan
            End Get
            Set(ByVal value As String)
                _taiKhoan = value
            End Set
        End Property
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        Private _laiSuat As Integer
        Property LaiSuat() As Integer
            Get
                Return _laiSuat
            End Get
            Set(ByVal value As Integer)
                _laiSuat = value
            End Set
        End Property
        Private _kyHan As Integer
        Property KyHan() As Integer
            Get
                Return _kyHan
            End Get
            Set(ByVal value As Integer)
                _kyHan = value
            End Set
        End Property
        Private _thangDaThu As Integer
        Property ThangDaThu() As Integer
            Get
                Return _thangDaThu
            End Get
            Set(ByVal value As Integer)
                _thangDaThu = value
            End Set
        End Property
        Private _tuDongGiaHan As Boolean
        Property TuDongGiaHan() As Boolean
            Get
                Return _tuDongGiaHan
            End Get
            Set(ByVal value As Boolean)
                _tuDongGiaHan = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal ngdung As Integer) As List(Of SoTietKiem)
        Dim ds As New List(Of SoTietKiem)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from SoTietKiem where NguoiDung=@NguoiDung", con)
            cmd.Parameters.AddWithValue("@NguoiDung", ngdung)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As SoTietKiem = New SoTietKiem()
                p.ID = rd.GetInt32(0)
                p.NguoiDung = rd.GetInt32(1)
                p.NgayLap = rd.GetDateTime(2)
                p.TaiKhoan = rd.GetString(3)
                p.SoTien = rd.GetInt32(4)
                p.LaiSuat = rd.GetInt32(5)
                p.KyHan = rd.GetInt32(6)
                p.ThangDaThu = rd.GetInt32(7)
                p.TuDongGiaHan = rd.GetBoolean(8)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As SoTietKiem
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from SoTietKiem where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As SoTietKiem = New SoTietKiem()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.NguoiDung = rd.GetInt32(1)
                p.NgayLap = rd.GetDateTime(2)
                p.TaiKhoan = rd.GetString(3)
                p.SoTien = rd.GetInt32(4)
                p.LaiSuat = rd.GetInt32(5)
                p.KyHan = rd.GetInt32(6)
                p.ThangDaThu = rd.GetInt32(7)
                p.TuDongGiaHan = rd.GetBoolean(8)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByRef p As SoTietKiem) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into SoTietKiem(NguoiDung, NgayLap, TaiKhoan, SoTien, LaiSuat,KyHan, ThangDaThu, TuDongGiaHan) values(@NguoiDung, @NgayLap, @TaiKhoan, @SoTien, @LaiSuat, @KyHan, @ThangDaThu, @TuDongGiaHan)", con)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@NgayLap", p.NgayLap)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LaiSuat", p.LaiSuat)
            cmd.Parameters.AddWithValue("@KyHan", p.KyHan)
            cmd.Parameters.AddWithValue("@ThangDaThu", p.ThangDaThu)
            cmd.Parameters.AddWithValue("@TuDongGiaHan", p.TuDongGiaHan)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@IDENTITY"
            p.ID = cmd.ExecuteScalar()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As SoTietKiem) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from SoTietKiem where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal p As SoTietKiem) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update SoTietKiem set NguoiDung=@NguoiDung, NgayLap=@NgayLap, TaiKhoan=@TaiKhoan, SoTien=@SoTien, LaiSuat=@LaiSuat, KyHan=@KyHan, ThangDaThu=@ThangDaThu, TuDongGiaHan=@TuDongGiaHan where ID = @ID", con)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@NgayLap", p.NgayLap)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LaiSuat", p.LaiSuat)
            cmd.Parameters.AddWithValue("@KyHan", p.KyHan)
            cmd.Parameters.AddWithValue("@ThangDaThu", p.ThangDaThu)
            cmd.Parameters.AddWithValue("@TuDongGiaHan", p.TuDongGiaHan)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Function GiaHan(ByVal u As Integer) As Integer
        Dim count = 0
        Try
            Dim ds = XL_SoTietKiem.LayDS(u)
            For Each so In ds
                If so.TuDongGiaHan = True And so.NgayLap.AddMonths(so.KyHan) < Now.Date Then
                    so.NgayLap = so.NgayLap.AddMonths(so.KyHan)
                    XL_SoTietKiem.CapNhat(so)
                    count += 1
                End If
            Next
        Catch ex As Exception
            Return -1
        End Try
        Return count
    End Function

    Function PhatSinhPhieuThu(ByVal u As Integer) As Integer
        Dim count = 0
        Try
            Dim ds = XL_SoTietKiem.LayDS(u)
            For Each so In ds
                Dim t = Now.Month - so.NgayLap.Month - so.ThangDaThu
                If t < 0 Then
                    t += 12
                End If
                If t > 0 And so.ThangDaThu <= so.KyHan Then
                    count += t
                    For i = 1 To t
                        Dim p = New PhieuThu
                        p.Ngay = so.NgayLap.AddMonths(i)
                        p.SoTien = so.SoTien * so.LaiSuat / 100
                        p.TaiKhoan = 0
                        p.NguoiThu = u
                        p.NguoiLap = u
                        p.LoaiThu = 0
                        XL_PhieuThu.Them(p)
                    Next
                    so.ThangDaThu += t
                    XL_SoTietKiem.CapNhat(so)
                End If
            Next
        Catch ex As Exception
            Return 0
        End Try
        Return count
    End Function
End Module
